package net.cohoivang.core;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;

import net.cohoivang.web.OurConnection;
import net.cohoivang.web.OurPrepareStatement;
import net.cohoivang.web.OurStatement;

public class ProfileDatabase {
	
	private static ProfileDatabase instance;
	private OurConnection connection;	
	
	private ProfileDatabase() {
		
	}
	
	public static ProfileDatabase getInstance() {
		return instance;
	}
	
	public static void initialize(OurConnection connection) {
		instance = new ProfileDatabase();
		instance.connection = connection;
	}
	
	public void loadAllSector() throws SQLException {
		System.out.println("... load all sector ...");
		OurStatement stmt = connection.createOurStatement();
		ResultSet rs = stmt.executeQuery("select * from sector");
		int count = 0;
		while (rs.next()) {
			ProfileCompany.getInstance().createSector(rs.getInt("id"), rs.getString("sector_name"));
			count ++;
		}
		System.out.println("loaded " + count + " sector");
		stmt.close();
	}
	
	public void loadAllIndustry() throws SQLException {
		System.out.println("select * from industry");
		OurStatement stmt = connection.createOurStatement();
		ResultSet rs = stmt.executeQuery("select * from industry");
		int count = 0;
		while (rs.next()) {
			ProfileCompany.getInstance().createIndustry(rs.getInt("sector"), 
														rs.getInt("industry_id"), 
														rs.getString("industry_name"));
			count++;
		}
		stmt.close();
		System.out.println("loaded " + count + " industries");
	}
	
	public void loadAllCompany() throws SQLException {
		System.out.println("load all company...");
		
		OurStatement stmt = connection.createOurStatement();
		ResultSet rs = stmt.executeQuery("select * from company");
		
		int count = 0;
		while (rs.next()) {
			Company company = ProfileCompany.getInstance().createCompany(rs.getInt("industry_id"), 
					rs.getString("legal_name"), rs.getString("english_name"));
			company.companyId = rs.getInt("id");
			company.shortName = rs.getString("short_name");
			company.address1 = rs.getString("address1");
			company.phone = rs.getString("phone");
			company.fax = rs.getString("fax");
			company.email = rs.getString("fax");
			company.website = rs.getString("website");
			company.description = rs.getString("description");
			company.createDate = rs.getDate("date_create");
			company.updateDate = rs.getDate("date_update");
			count++;
		}
		stmt.close();
		System.out.println("loaded " + count + " companies");
	}
	
	public int insertCompany(String legalName, String englishName, String shortName, String address1, String phone, String fax,
			String email, String website, String description, int industry, Date createDate, Date updateDate, int sector) throws SQLException {
		
		String sqlPrepare =
			"INSERT INTO company (`legal_name`, `english_name`, `short_name`," +
			"`address1`, `phone`, `fax`, `email`, `website`, " +					
			"`description`, `industry_id`,`sector_id`,`date_create`,`date_update`)" +
			" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		OurPrepareStatement insertCompany = connection.createOurPrepareStatement(sqlPrepare);
		
		int count = 0;
		insertCompany.setString(++count, legalName);
		insertCompany.setString(++count, englishName);
		insertCompany.setString(++count, shortName);
		insertCompany.setString(++count, address1);
		insertCompany.setString(++count, phone);
		insertCompany.setString(++count, fax);
		insertCompany.setString(++count, email);
		insertCompany.setString(++count, website);
		insertCompany.setString(++count, description);
		insertCompany.setInt(++count, industry);
		insertCompany.setInt(++count, sector);
		insertCompany.setDate(++count, updateDate);
		insertCompany.setDate(++count, updateDate);
		insertCompany.executeUpdate();
		ResultSet keys = insertCompany.getGenerateKeys();
		int generatedId = -1;
		if (keys.next()) {
			generatedId = keys.getInt(1);
		}
		insertCompany.close();			
		return generatedId;
	}
		
	
}
